package com.china08.yxyapi.schedule.lesson;

import java.net.UnknownHostException;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.stereotype.Component;

import com.china08.yxyapi.service.yxyresource.YxyResourceService;
import com.china08.yxyapi.util.YxyScheduleRunnableUtil;

@Component
@EnableScheduling
public class LessonFindVideoAllDurationSchedule {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    YxyResourceService yxyResourceServiceImpl;

    // @Scheduled(fixedRate = 1000 * 60 * 5)
    // @Scheduled(cron = "0 15 3 * * ?")
    public void execute1() throws UnknownHostException {

        // ##
        // 检测定时任务是否可执行
        if (!YxyScheduleRunnableUtil.checkRunnable()) { return; }

        System.out.println("[开始计算校本课程时长开始]");

        // 获取需要计算的，转码成功视频
        StringBuffer sb = new StringBuffer();
        sb.append("   select                                                        ");
        sb.append("     t2.lesson_id as lessonId                                    ");
        sb.append("   from yxy_lessons.yxy_lessons_video t1                         ");
        sb.append("     left join yxy_lessons.yxy_lessons_school_lesson_item t2     ");
        sb.append("       on (t1.id = t2.video_id)                                  ");
        sb.append("   where t1.status = 2                                           ");
        sb.append("       and t2.status != -1                                       ");
        sb.append("       and t1.duration_calculate_flag = 0                        ");
        List<Map<String, Object>> lessonIdList = jdbcTemplate.queryForList(sb.toString());

        // 校本课程id
        Set<String> schoolLessonId = new HashSet<>();

        // 获取对用课时
        for (Map<String, Object> map : lessonIdList) {
            String lessonId = map.get("lessonId") + "";
            if (StringUtils.isBlank(lessonId)) {
                continue;
            }
            // 去重复
            schoolLessonId.add(lessonId);
        }

        for (String lessonId : schoolLessonId) {
            // 获取课时总时长
            StringBuffer sb1 = new StringBuffer();
            sb1.append("   select                                                       ");
            sb1.append("     sum(t3.duration) as duration                               ");
            sb1.append("   from yxy_lessons.yxy_lessons_school_lesson t1                ");
            sb1.append("     left join yxy_lessons.yxy_lessons_school_lesson_item t2    ");
            sb1.append("       on (t1.id = t2.lesson_id)                                ");
            sb1.append("     left join yxy_lessons.yxy_lessons_video t3                 ");
            sb1.append("       on (t2.video_id = t3.id)                                 ");
            sb1.append("   where t1.id = ?                                              ");
            sb1.append("        and t2.status != -1                                     ");
            sb1.append("        and t3.status = 2                                       ");
            sb1.append("   group by t1.id                                               ");
            try {
                Map<String, Object> schoolMap = jdbcTemplate.queryForMap(sb1.toString(), new Object[] { lessonId });
                Object object = schoolMap.get("duration");
                if (object != null) {
                    Integer duration = Integer.parseInt(object + "");
                    String upd = " update yxy_lessons.yxy_lessons_school_lesson set duration = ? where id = ? ";
                    jdbcTemplate.update(upd, new Object[] { duration, lessonId });
                }
            } catch (Exception e) {
            }
        }
        System.out.println("[开始计算校本课程时长结束]");
    }

    // @Scheduled(fixedRate = 1000 * 60 * 5)
    // @Scheduled(cron = "0 15 3 * * ?")
    public void execute2() throws UnknownHostException {

        System.out.println("[开始计算专题课程时长开始]");

        // 获取需要计算的，转码成功视频
        StringBuffer sb = new StringBuffer();
        sb.append("   select                                                        ");
        sb.append("     t2.lesson_id as lessonId                                    ");
        sb.append("   from yxy_lessons.yxy_lessons_video t1                         ");
        sb.append("     left join yxy_lessons.yxy_lessons_topic_lesson_item t2      ");
        sb.append("       on (t1.id = t2.video_id)                                  ");
        sb.append("   where t1.status = 2                                           ");
        sb.append("       and t2.status != -1                                       ");
        sb.append("       and t1.duration_calculate_flag = 0                        ");
        List<Map<String, Object>> lessonIdList = jdbcTemplate.queryForList(sb.toString());

        // 专题课程id
        Set<String> topicLessonId = new HashSet<>();

        for (Map<String, Object> map : lessonIdList) {
            String lessonId = map.get("lessonId") + "";
            if (StringUtils.isBlank(lessonId)) {
                continue;
            }
            // 去重复
            topicLessonId.add(lessonId);
        }

        for (String lessonId : topicLessonId) {
            // 课程总时常
            Integer duration1 = 0;
            // 微课总时常
            Integer duration2 = 0;

            // 获取课程总时常
            StringBuffer sb1 = new StringBuffer();
            sb1.append("   select                                                       ");
            sb1.append("     sum(t3.duration) as duration                               ");
            sb1.append("   from yxy_lessons.yxy_lessons_topic_lesson t1                 ");
            sb1.append("     left join yxy_lessons.yxy_lessons_topic_lesson_item t2     ");
            sb1.append("       on (t1.id = t2.lesson_id)                                ");
            sb1.append("     left join yxy_lessons.yxy_lessons_video t3                 ");
            sb1.append("       on (t2.video_id = t3.id)                                 ");
            sb1.append("   where t1.id = ?                                              ");
            sb1.append("        and t2.status != -1                                     ");
            sb1.append("        and t3.status = 2                                       ");
            sb1.append("   group by t1.id                                               ");

            try {
                Map<String, Object> topicMap = jdbcTemplate.queryForMap(sb1.toString(), new Object[] { lessonId });
                Object object = topicMap.get("duration");
                if (object != null) {
                    duration1 = Integer.parseInt(object.toString());
                }
            } catch (Exception e) {
            }

            // 获取微课总时常
            StringBuffer sb2 = new StringBuffer();
            sb2.append("  select                                                        ");
            sb2.append("    sum(t3.duration) as duration                                ");
            sb2.append("  from yxy_lessons.yxy_lessons_topic_lesson t1                  ");
            sb2.append("    left join yxy_lessons.yxy_lessons_topic_lesson_item t2      ");
            sb2.append("      on (t1.id = t2.lesson_id)                                 ");
            sb2.append("    left join yxy_res.yxy_res_resource t3                       ");
            sb2.append("      on (t2.weike_id = t3.id)                                  ");
            sb2.append("  where t1.id = ?                                               ");
            sb2.append("        and t2.status = 2                                       ");
            sb2.append("        and t3.convert_status = 2                               ");
            sb2.append("  group by t1.id                                                ");
            try {
                Map<String, Object> topicMap = jdbcTemplate.queryForMap(sb2.toString(), new Object[] { lessonId });
                Object object = topicMap.get("duration");
                if (object != null) {
                    duration2 = Integer.parseInt(object.toString());
                }
            } catch (Exception e) {
            }

            // 修改
            String upd = " update yxy_lessons.yxy_lessons_topic_lesson set duration = ? where id = ? ";
            jdbcTemplate.update(upd, new Object[] { duration1 + duration2, lessonId });
        }
        System.out.println("[开始计算专题课程时长结束]");
    }
}
